//
//  DbInisializer.m
//  DataCollector
//
//  Created by jiro on 12/06/09.
//  Copyright 2012 jiro music. All rights reserved.
//

#import "DbInisializer.h"

#import "../Commons/Commons.h"
#import "../DbTypes/DbTypes.h"
#import "libpq-fe.h"

@implementation DbInisializer

+ (void) createTables: (DbOperator*) dbOpe
{
	PGresult* result = NULL;

	@try
	{
		NSMutableArray* sqls = [ NSMutableArray array ];
		[ sqls addObject: [ DataCollectLog queryCreateTable ] ];
		[ sqls addObject: [ StockBrandJa queryCreateTable ] ];
		[ sqls addObject: [ StockBrandCategoryJa queryCreateTable ] ];
		[ sqls addObject: [ StockUnitDivitionJa queryCreateTable ] ];
		[ sqls addObject: [ StockConfidenceRemainJa queryCreateTable ] ];
		[ sqls addObject: [ StockExchangeJa queryCreateTable ] ];
		[ sqls addObject: [ CurrencyType queryCreateTable ] ];
		[ sqls addObject: [ CurrencyExchangeType queryCreateTable ] ];
		[ sqls addObject: [ CurrencyExchangeJa queryCreateTable ] ];
		[ sqls addObject: [ NationalBondsJa queryCreateTable ] ];
		[ sqls addObject: [ NationalBondsExchangeJa queryCreateTable ] ];
		[ sqls addObject: [ CrudeOilProductCountryCode queryCreateTable ] ];
		[ sqls addObject: [ CrudeOilCode queryCreateTable ] ];
		[ sqls addObject: [ CrudeOilExchange queryCreateTable ] ];
		[ sqls addObject: [ CurrencyExchangeJaMa queryCreateTable ] ];
		[ sqls addObject: [ CurrencyExchangeGmo queryCreateTable ] ];
		[ sqls addObject: [ CurrencyExchangeGmoMa queryCreateTable ] ];
		[ sqls addObject: [ CurrencyExchangeLiquidityGmo queryCreateTable ] ];
		[ sqls addObject: [ CurrencyExchangeLiquidityMaGmo queryCreateTable ] ];
		NSString* s;
		NSEnumerator* e = [ sqls objectEnumerator ]; 
		while ( s = [ e nextObject ] )
		{
			NSLog( @"%@", s );
			DbResponseType dbRes = [ dbOpe executeQuery: s intoResult: & result ];
			if ( DbResponse_Success != dbRes and DbResponse_SuccessWithWarning != dbRes )
			{
				@throw( [ NSException exceptionWithName: @"" reason: @"" userInfo: nil ] );
			}
		}
	}
	@catch ( NSException* e )
	{
		@throw( [ NSException 
			exceptionWithName: @"create table failed"
			reason: [ NSString stringWithFormat: @"%@, %@", [ e reason ], [ dbOpe errorMessage ] ]
			userInfo: nil ] );
	}
}

+ (void) insertInitialValues: (DbOperator*) dbOpe
{
	NSMutableArray* tableNames = [ NSMutableArray array ];
	NSMutableArray* values = [ NSMutableArray array ];

	[ tableNames addObject: [ DataCollectLog tableName ] ];
	[ values addObject: [ DataCollectLog queryInsertInitialValues ] ];
	
	[ tableNames addObject: [ StockBrandJa tableName ] ];
	[ values addObject: [ StockBrandJa queryInsertInitialValues ] ];

	[ tableNames addObject: [ StockBrandCategoryJa tableName ] ];
	[ values addObject: [ StockBrandCategoryJa queryInsertInitialValues ] ];

	[ tableNames addObject: [ StockUnitDivitionJa tableName ] ];
	[ values addObject: [ StockUnitDivitionJa queryInsertInitialValues ] ];

	[ tableNames addObject: [ StockConfidenceRemainJa tableName ] ];
	[ values addObject: [ StockConfidenceRemainJa queryInsertInitialValues ] ];

	[ tableNames addObject: [ CurrencyType tableName ] ];
	[ values addObject: [ CurrencyType queryInsertInitialValues ] ];

	[ tableNames addObject: [ CurrencyExchangeType tableName ] ];
	[ values addObject: [ CurrencyExchangeType queryInsertInitialValues ] ];
		
	[ tableNames addObject: [ NationalBondsJa tableName ] ];
	[ values addObject: [ NationalBondsJa queryInsertInitialValues ] ];

	[ tableNames addObject: [ NationalBondsExchangeJa tableName ] ];
	[ values addObject: [ NationalBondsExchangeJa queryInsertInitialValues ] ];

	[ tableNames addObject: [ CrudeOilProductCountryCode tableName ] ];
	[ values addObject: [ CrudeOilProductCountryCode queryInsertInitialValues ] ];

	[ tableNames addObject: [ CrudeOilCode tableName ] ];
	[ values addObject: [ CrudeOilCode queryInsertInitialValues ] ];

	[ tableNames addObject: [ CrudeOilExchange tableName ] ];
	[ values addObject: [ CrudeOilExchange queryInsertInitialValues ] ];

	[ tableNames addObject: [ CurrencyExchangeJaMa tableName ] ];
	[ values addObject: [ CurrencyExchangeJaMa queryInsertInitialValues ] ];

	@try
	{
		NSEnumerator* e = [ values objectEnumerator ];
		NSString* value;
		int i = 0;
		while ( value = [ e nextObject ] )
		{
			if ( value == nil || [ value isEqualToString: @"" ] )
			{
				i++;
				continue;
			}

			NSLog( @"%@", [ tableNames objectAtIndex: i  ] );

			PGresult* res = nil;
			NSString* query = [ NSString stringWithFormat:
				@"insert into %@ values %@;",
				[ tableNames objectAtIndex: i ], value ];
			DbResponseType dbRes = [ dbOpe executeQuery: query intoResult: & res ];
			if ( DbResponse_Success != dbRes and DbResponse_SuccessWithWarning != dbRes )
			{
				@throw( [ NSException exceptionWithName: @"" reason: @"" userInfo: nil ] );
			}
			i++;
		}
	}
	@catch ( NSException* e )
	{
		@throw( [ NSException
			exceptionWithName: @"insert value failed"
			reason: [ NSString stringWithFormat: @"%@, %@", [ e reason ], [ dbOpe errorMessage ] ]
			userInfo: nil ] );
	}

	// update stock brand

	// ----- data collect log  ---- //	
	dbOpe = [ DbOperator defaultOperator ];
	PGresult* result = nil;
	DbResponseType dbResponse;
	NSString* query;
	NSEnumerator* e;
	// ---- //

	@try
	{
		//	initialize data collect log for currency
		query = [ CurrencyExchangeType querySelectAll ];

		dbResponse = [ dbOpe executeQuery: query intoResult: & result ];
		if	( DbResponse_Success != dbResponse and DbResponse_SuccessWithWarning != dbResponse )
		{
			@throw( [ NSException exceptionWithName: @"currency exchange type select failed"
								reason: [ dbOpe errorMessage ]
								userInfo: nil ] );
		}
		CurrencyExchangeTypeList* typeList = [ CurrencyExchangeTypeList listWithPgResult: result ];
		CurrencyExchangeType* type;
		e = [ typeList objectEnumerator ];
		while ( type = [ e nextObject ] )
		{
			DataCollectLog* log = [ [ DataCollectLog alloc ] init ];
			log.code0 = type.code;
			log.code1 = type.targetCurrencyCode;
			log.marketDataType = MarketData::Type_CurrencyExchangeJa;
			log.latestDate = [ NSDate dateWithString: @"1980-1-1 00:00:00 +0900" ];
			NSString* query = [ log queryInsert ];
			dbResponse = [ dbOpe executeQuery: query intoResult: & result ];
			if ( DbResponse_Success != dbResponse and DbResponse_SuccessWithWarning != dbResponse )
			{
				@throw( [ NSException exceptionWithName: @"data_collect_log table insert failed"
									reason: [ dbOpe errorMessage ]
									userInfo: nil ] );
			}
		}
	}
	@catch ( NSException* e )
	{
		NSLog( @"%@", [ NSString stringWithFormat: @"%@: %@", [ e name ], [ e reason ] ] );
		
	}
}

+ (void) intializeDb: (NSString*) result
{
	result = @"";
	DbOperator* dbOpe = [ DbOperator defaultOperator ];
	@try
	{
		[ DbInisializer createTables: dbOpe ];
		[ DbInisializer insertInitialValues: dbOpe ];
	}
	@catch ( NSException* e )
	{
		result = [ NSString stringWithFormat: @"%@: %@", [ e name ], [ e reason ] ];
	}

	[ dbOpe disconnect ];
}

+ (void) createCurrencyMaValuesWithAverageLength: (int) averageLength;
{
	DbOperator* ope = [ DbOperator defaultOperator ];
	DbResponseType response;
	PGresult* result;
	NSString* query;
	NSEnumerator* e;

	query = [ CurrencyExchangeType querySelectAll ];
	CurrencyExchangeTypeList* currencyExchangeTypes;
	response = [ ope executeQuery: query intoResult: & result ];
	if ( response != DbResponse_Success && response != DbResponse_SuccessWithWarning )
	{
		@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );
	}
	currencyExchangeTypes =[ CurrencyExchangeTypeList listWithPgResult: result ];
	PQclear( result );

	e = [ currencyExchangeTypes objectEnumerator ];
	CurrencyExchangeType* currencyExchangeType;
	while ( currencyExchangeType = [ e nextObject ]   )
	{
		NSLog( @"createCurrencyMaValuesWithAverageLength: %@, %@, %d", currencyExchangeType.code, currencyExchangeType.targetCurrencyCode, averageLength );
		ExpressionNode* codeCondition = [ ExpressionNode 
			nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_Code ]
			operatorType: BinaryOperator::Type_Equal
			rightNode: [ StringValueNode nodeWithStringValue: currencyExchangeType.code ] ];
		ExpressionNode* targetCodeCondition = [ ExpressionNode 
			nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_TargetCurrencyCode ]
			operatorType: BinaryOperator::Type_Equal
			rightNode: [ StringValueNode nodeWithStringValue: currencyExchangeType.targetCurrencyCode ] ];
		ExpressionNode* averageLengthCondition = [ ExpressionNode 
			nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_AverageLength ]
			operatorType: BinaryOperator::Type_Equal
			rightNode: [ NumberValueNode nodeWithNumberValue: averageLength ] ];
		ExpressionNode* codeConditions = [ ExpressionNode 
			nodeWithLeftNode: codeCondition
			operatorType: BinaryOperator::Type_Equal
			rightNode: targetCodeCondition ];
		ExpressionNode* conditionNode = [ ExpressionNode 
			nodeWithLeftNode: codeConditions
			operatorType: BinaryOperator::Type_And
			rightNode: averageLengthCondition ];
		WhereNode* whereNode = [ WhereNode nodeWithConditionNode: conditionNode ];
		ExpressionNode* fieldNode = [ FieldNode nodeWithFieldName: kText_ExchangeDate ];
		OrderByNode* orderByNodeAsc = [ OrderByNode nodeWithFields: fieldNode isAsc: YES ];
		CurrencyExchangeJaList* listJa;

		result = nil;
		query = [ CurrencyExchangeJaMa 
			querySelectFields: [ NSArray arrayWithObject: [ FieldNode nodeWithFieldName: kText_CountForAsterisk ] ] 
			andClauses: [ NSArray arrayWithObjects: whereNode, nil ] ];
//		NSLog( @"%@", query );
		if ( DbResponse_Success != [ ope executeQuery: query intoResult: & result ] )
		{
			@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );	
		}
		int dataCount = atoi( PQgetvalue( result, 0, 0 ) );
		PQclear( result );
		if ( dataCount == 0 )
		{
			ExpressionNode* conditionNode =
				[ ExpressionNode nodeWithLeftNode: codeCondition
					operatorType: BinaryOperator::Type_And 
					rightNode: targetCodeCondition ];
			WhereNode* whereNode = [ WhereNode nodeWithConditionNode: conditionNode ];
			NSArray* clauses = [ NSArray arrayWithObjects: whereNode, orderByNodeAsc, nil ];
			query = [ CurrencyExchangeJa querySelectAllWithClauses: clauses ];
//			NSLog( @"%@", query );
			response = [ ope executeQuery: query intoResult: & result ];
			if ( response != DbResponse_Success && response != DbResponse_SuccessWithWarning )
			{
				@throw( [ NSException exceptionWithName: @"Analyzer createMaValues" reason: [ ope errorMessage ] userInfo: nil ] );
			}
			listJa = [ CurrencyExchangeJaList listWithPgResult: result ];
			PQclear( result );

			//	前提チェック
			if ( [ listJa count ] < averageLength )
			{
				continue;
			}
		}
		else 
		{	//	update data
			OrderByNode* orderByNodeDesc = [ OrderByNode nodeWithFields: [ FieldNode nodeWithFieldName: kText_ExchangeDate ] isAsc: NO ];
			LimitNode* limitNode = [ LimitNode nodeWithLimit: 1 ];
			NSArray* clauses = [ NSArray arrayWithObjects: whereNode, orderByNodeDesc, limitNode, nil ];
			query = [ CurrencyExchangeJaMa querySelectAllWithClauses: clauses ];
//			NSLog( @"%@", query );
			response = [ ope executeQuery: query intoResult: & result ];
			if ( response != DbResponse_Success )
			{
				@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );
			}
			CurrencyExchangeJaMaList* listJaMa = [ CurrencyExchangeJaMaList listWithPgResult: result ];
			PQclear( result );

			ExpressionNode* codeConditionsNode = [ ExpressionNode 
				nodeWithLeftNode: codeCondition
				operatorType: BinaryOperator::Type_And
				rightNode: targetCodeCondition ];

			WhereNode* whereNode2 = [ WhereNode nodeWithConditionNode: codeConditionsNode ];
			NSArray* clauesX = [ NSArray arrayWithObjects: whereNode2, orderByNodeDesc, limitNode, nil ];
			query = [ CurrencyExchangeJa querySelectAllWithClauses: clauesX ];
//			NSLog( @"%@", query );
			response = [ ope executeQuery: query intoResult: & result ];
			if ( response != DbResponse_Success )
			{
				@throw( [ NSException exceptionWithName: @"Analyzer createMaValues" reason: [ ope errorMessage ] userInfo: nil ] );
			}
			listJa = [ CurrencyExchangeJaList listWithPgResult: result ];
			PQclear( result );
			if ( [ listJa count ] == 0 )
			{
				continue;
			}

			CurrencyExchangeJa* ja = [ listJa objectAtIndex: 0 ];
			CurrencyExchangeJaMa* jama = [ listJaMa objectAtIndex: 0 ];

//			NSLog( @"%@", jama.exchangeDate );
//			NSLog( @"%@", ja.exchangeDate );
			if ( [ ja.exchangeDate isEqualToDate: jama.exchangeDate ] )
			{
				continue;
			}

			int timeInterval = [ ja.exchangeDate timeIntervalSinceDate: jama.exchangeDate ];
			timeInterval += secondsForOneDay * averageLength;

			NSDate* startDate = [ NSDate dateWithTimeInterval: - timeInterval sinceDate: ja.exchangeDate ];
			NSDate* endDate = ja.exchangeDate;

			ExpressionNode* dateCondition0 = [ ExpressionNode 
				nodeWithLeftNode: [ StringValueNode nodeWithStringValue: [ startDate description ] ]
				operatorType: BinaryOperator::Type_LessOrEqual
				rightNode: [ FieldNode nodeWithFieldName: kText_ExchangeDate ] ];
			ExpressionNode* dateCondition1 = [ ExpressionNode 
				nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_ExchangeDate ]
				operatorType: BinaryOperator::Type_LessOrEqual
				rightNode: [ StringValueNode nodeWithStringValue: [ endDate description ] ] ];
			ExpressionNode* dateCondition = [ ExpressionNode 
				nodeWithLeftNode: dateCondition0
				operatorType: BinaryOperator::Type_And
				rightNode: dateCondition1 ];
			ExpressionNode* conditionNode = [ ExpressionNode
				nodeWithLeftNode: dateCondition
				operatorType: BinaryOperator::Type_And
				rightNode: codeConditionsNode ];	
			WhereNode* whereNode = [ WhereNode nodeWithConditionNode: conditionNode ];
			NSArray* clauses2 = [ NSArray arrayWithObjects: whereNode, orderByNodeAsc, nil ];
			query = [ CurrencyExchangeJa querySelectAllWithClauses: clauses2 ];

			response = [ ope executeQuery: query intoResult: & result ];
			if ( response != DbResponse_Success )
			{
				@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );
			}
			listJa = [ CurrencyExchangeJaList listWithPgResult: result ];
			PQclear( result );
			
			if ( [ listJa count ] < averageLength )
			{
				continue;
			}
		}
//		NSLog( @"%@", [ [ listJa objectAtIndex: 0 ] exchangeDate ] );

		for ( int i = averageLength - 1, count = [ listJa count ]; i < count; i++ )
		{
			double start = 0.0;
			double high = 0.0;
			double low = 0.0;
			double end = 0.0;
			for ( int j = i - averageLength + 1; j < i + 1; j++ )
			{
				CurrencyExchangeJa* ja = [ listJa objectAtIndex: j ];
				start += ja.openPrice;
				high += ja.highPrice;
				low += ja.lowPrice;
				end += ja.closePrice;
			}
			start /= averageLength;
			high /= averageLength;
			low /= averageLength;
			end /= averageLength;
			
			CurrencyExchangeJa* ja = [ listJa objectAtIndex: i ];
			CurrencyExchangeJaMa* jaMa = [ [ CurrencyExchangeJaMa alloc ] init ];
			jaMa.code = ja.code;
			jaMa.targetCurrencyCode = ja.targetCurrencyCode;
			jaMa.exchangeDate = ja.exchangeDate;
			jaMa.openPrice = start;
			jaMa.highPrice = high;
			jaMa.lowPrice = low;
			jaMa.closePrice = end;
			jaMa.averageLength = averageLength;
			query = [ jaMa queryInsert ];
			result = nil;
			response = [ ope executeQuery: query intoResult: & result ];
			if ( response != DbResponse_Success )
			{
				@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );
			}
			if ( result != nil ) { PQclear( result ); }
		}
	}
	[ ope disconnect ];
}

+ (void) createCurrencyLiquidityData
{
	DbOperator* ope = [ DbOperator defaultOperator ];
	NSString* s;
	PGresult* r;
	NSEnumerator* e;
	
	//	get currency exchnage type
	s = [ CurrencyExchangeType querySelectAll ];
	if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
	{
		@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );		
	}
	CurrencyExchangeTypeList* types = [ CurrencyExchangeTypeList listWithPgResult: r ];
	PQclear( r );

	FieldNode* countNode = [ FieldNode nodeWithFieldName: kText_CountForAsterisk ];
	WhereNode* whereNode;
	ExpressionNode* conditionNode;
	OrderByNode* orderByNode;
	LimitNode* limitNode = [ LimitNode nodeWithLimit: 1 ];
 
	e = [ types objectEnumerator ];
	CurrencyExchangeType* type;
	while ( type = [ e nextObject ] )
	{
		NSLog( @"%@%@", type.code, type.targetCurrencyCode );
		FieldNode* exchangeDateNode = [ FieldNode nodeWithFieldName: kText_ExchangeDate ];
		FieldNode* codeNode = [ FieldNode nodeWithFieldName: kText_Code ];
		FieldNode* targetCodeNode = [ FieldNode nodeWithFieldName: kText_TargetCurrencyCode ];
		StringValueNode* codeValueNode = [ StringValueNode nodeWithStringValue: type.code ];
		StringValueNode* targetCodeValueNode = [ StringValueNode nodeWithStringValue: type.targetCurrencyCode ];
		ExpressionNode* codeCondition = [ ExpressionNode
			nodeWithLeftNode: codeNode operatorType: BinaryOperator::Type_Equal rightNode: codeValueNode  ];
		ExpressionNode* targetCodeCondition = [ ExpressionNode
			nodeWithLeftNode: targetCodeNode operatorType: BinaryOperator::Type_Equal rightNode: targetCodeValueNode ];
		ExpressionNode* codeConditions = [ ExpressionNode
			nodeWithLeftNode: codeCondition operatorType: BinaryOperator::Type_And rightNode: targetCodeCondition ];
		whereNode = [ WhereNode nodeWithConditionNode: codeConditions ];

		//	get latest date
		orderByNode = [ OrderByNode nodeWithFieldName: kText_ExchangeDate isAsc: NO ];
		s = [ CurrencyExchangeGmo querySelectAllWithClauses: [ NSArray arrayWithObjects: whereNode, orderByNode, limitNode,	nil ] ];
//		NSLog( @"%@", s );
		if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
		{
			@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );		
		}
		CurrencyExchangeJaList* jaList = [ CurrencyExchangeJaList listWithPgResult: r ];
		PQclear( r );
		if ( [ jaList count ] == 0 )
		{
			continue;
		}
		CurrencyExchangeGmo* gmo = [ jaList objectAtIndex: 0 ];
		NSDate* newestDate = [ gmo.exchangeDate dateByAddingTimeInterval: secondsForOneMinuet ];
		gmo = nil;

		//	get data count
		s = [ CurrencyExchangeLiquidityGmo querySelectFields: [ NSArray arrayWithObject: countNode ] 
				andClauses: [ NSArray arrayWithObject: whereNode ] ];
		if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
		{
			@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );		
		}
		int dataCount =  atoi( PQgetvalue( r,  0, 0 ) );
		PQclear( r );

		//	set start date time
		NSDate* startDate;
		if ( dataCount == 0 )
		{
			startDate = [ NSDate dateWithNaturalLanguageString: @"2007-1-1 07:00:00 +0900" ];
		}
		else
		{
			orderByNode = [ OrderByNode nodeWithFieldName: kText_ExchangeDate isAsc: NO ];
			s = [ CurrencyExchangeGmo querySelectAllWithClauses: [ NSArray arrayWithObjects: whereNode, orderByNode, limitNode, nil ] ];  
			if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
			{
				@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );
			}
			CurrencyExchangeJaList* gmoList = [ CurrencyExchangeJaList listWithPgResult: r ];
			PQclear( r );
			if ( [ gmoList count ] == 0 )
			{
				continue;
			}

			CurrencyExchangeGmo* gmo = [ gmoList objectAtIndex: 0 ];
			startDate = [ gmo.exchangeDate dateByAddingTimeInterval: secondsForOneMinuet ];
//			NSLog( @"%@", startDate );
		}

		while ( NO == [ startDate isEqualToDate: newestDate ] )
		{
			NSLog( @"%@ ---> %@", startDate, newestDate );
			//	get 1 min candle data of 1 day
			DateValueNode* startDateNode = [ DateValueNode nodeWithDate: startDate ];
			DateValueNode* endDateNode = [ DateValueNode nodeWithDate: [ startDate dateByAddingTimeInterval: secondsForOneDay ] ];
			ExpressionNode* startDateCondition = [ ExpressionNode 
				nodeWithLeftNode: startDateNode operatorType: BinaryOperator::Type_LessOrEqual rightNode: exchangeDateNode ];
			ExpressionNode* endDateCondition = [ ExpressionNode 
				nodeWithLeftNode: exchangeDateNode operatorType: BinaryOperator::Type_Less rightNode: endDateNode ];
			ExpressionNode* dateConditions = [ ExpressionNode
				nodeWithLeftNode: startDateCondition operatorType: BinaryOperator::Type_And rightNode: endDateCondition ];
			conditionNode = [ ExpressionNode
				nodeWithLeftNode: codeConditions operatorType: BinaryOperator::Type_And rightNode: dateConditions ];
			whereNode = [ WhereNode nodeWithConditionNode: conditionNode ];
			orderByNode = [ OrderByNode nodeWithFieldName: kText_ExchangeDate isAsc: YES ];
			s = [ CurrencyExchangeGmo querySelectAllWithClauses:
					[ NSArray arrayWithObjects: whereNode, orderByNode, nil ] ];
//			NSLog( @"%@", s );
			if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
			{
				@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );
			}
			CurrencyExchangeJaList* list = [ CurrencyExchangeJaList listWithPgResult: r ];
			PQclear( r );
			if ( [ list count ] == 0 || [ list count ] < 2 )
			{
				startDate = [ startDate dateByAddingTimeInterval: secondsForOneDay ];
				continue;
			}

			double liquidity = 0.0;
			for ( int i = 1, count = [ list count ]; i < count; i++ )
			{
				CurrencyExchangeGmo* gmo = [ list objectAtIndex: i ];
				CurrencyExchangeGmo* gmoPrev = [ list objectAtIndex: i - 1 ];

				liquidity += fabs( gmo.highPrice - gmoPrev.highPrice );
				liquidity += fabs( gmo.lowPrice - gmoPrev.lowPrice );
			}
			liquidity /= 2.0;
//			liquidity /= [ list count ];
			
			CurrencyExchangeLiquidityGmo* liquidityGmo = [ [ CurrencyExchangeLiquidityGmo alloc ] init ];
			liquidityGmo.code = type.code;
			liquidityGmo.targetCurrencyCode = type.targetCurrencyCode;
			liquidityGmo.exchangeDate = [ startDate dateByAddingTimeInterval: - 7.0 * secondsForOneHour ];
//			liquidityGmo.exchangeDate = [ startDate dateByAddingTimeInterval: secondsForOneMinuet - 6 * secondsForOneHour - secondsForOneDay ];
			liquidityGmo.liquidity = liquidity;
//			NSLog( @"%@", [ liquidityGmo queryInsertValue ] );
			s = [ liquidityGmo queryInsert ];
			if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
			{
				@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );
			}
			PQclear( r );

			startDate = [ startDate dateByAddingTimeInterval: secondsForOneDay ];
		}
	}
}

+ (void) createCurrencyLiquidityMaData: (int) averageLength
{
	DbOperator* ope = [ DbOperator defaultOperator ];
	PGresult* r;

	NSString* s =[ CurrencyExchangeType querySelectAll ];
	if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
	{
		@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );	
	}
	CurrencyExchangeTypeList* typeList = [ CurrencyExchangeTypeList listWithPgResult: r ];
	PQclear( r );

	NSEnumerator* e = [ typeList objectEnumerator ];
	CurrencyExchangeType* type;
	while ( type = [ e nextObject ] )
	{
		NSLog( @"createCurrencyLiquidityMaData: %@, %@, %d", type.code, type.targetCurrencyCode, averageLength );
		WhereNode* where;
		OrderByNode* orderBy = [ OrderByNode nodeWithFieldName: kText_ExchangeDate isAsc: YES ];
		ExpressionNode* codeCondition = [ ExpressionNode 
			nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_Code ]
			operatorType: BinaryOperator::Type_Equal
			rightNode: [ StringValueNode nodeWithStringValue: type.code ] ];
		ExpressionNode* targetCodeCondition = [ ExpressionNode 
			nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_TargetCurrencyCode ]
			operatorType: BinaryOperator::Type_Equal 
			rightNode: [ StringValueNode nodeWithStringValue: type.targetCurrencyCode ] ];
		ExpressionNode* codeConditions = [ ExpressionNode 
			nodeWithLeftNode: codeCondition operatorType: BinaryOperator::Type_And rightNode: targetCodeCondition ];
		ExpressionNode* averageLengthCondition = [ ExpressionNode \
			nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_AverageLength ] 
			operatorType: BinaryOperator::Type_Equal
			rightNode: [ NumberValueNode nodeWithNumberValue: averageLength ] ];
		ExpressionNode* conditions = [ ExpressionNode
			nodeWithLeftNode: codeConditions operatorType: BinaryOperator::Type_And rightNode: averageLengthCondition ]; 
		where = [ WhereNode nodeWithConditionNode: conditions ];
		LimitNode* limit = [ LimitNode nodeWithLimit: 1 ];


		//	set start date
		NSDate* startDate;
		s = [ CurrencyExchangeLiquidityMaGmo querySelectAllWithClauses: [ NSArray arrayWithObjects: where, orderBy, limit, nil ] ];
		if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
		{
			@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );	
		}
		CurrencyExchangeLiquidityMaGmoList* maList = [ CurrencyExchangeLiquidityMaGmoList listWithPGresult: r ];
		PQclear( r );
		if ( [ maList count ] == 0 )
		{
			startDate = [ NSDate dateWithString: @"1990-1-1 00:00:00 +09" ];
		}
		else
		{
			CurrencyExchangeLiquidityMaGmo* maGmo = [ maList objectAtIndex: 0 ];
			startDate = [ maGmo.exchangeDate dateByAddingTimeInterval: secondsForOneDay ];			
			startDate = [ startDate dateByAddingTimeInterval: - secondsForOneDay * averageLength ];
		}

		//	select raw data
		ExpressionNode* startDateCondition = [ ExpressionNode 
			nodeWithLeftNode: [ FieldNode nodeWithFieldName: kText_ExchangeDate ]
			operatorType: BinaryOperator::Type_Greater 
			rightNode: [ DateValueNode nodeWithDate: startDate ] ];
		conditions = [ ExpressionNode nodeWithLeftNode: codeConditions operatorType: BinaryOperator::Type_And rightNode: startDateCondition ];
		where = [ WhereNode nodeWithConditionNode: conditions ];
		orderBy = [ OrderByNode nodeWithFieldName: kText_ExchangeDate isAsc: YES ];
		s = [ CurrencyExchangeLiquidityGmo querySelectAllWithClauses: [ NSArray arrayWithObjects: where, orderBy, nil ] ];
		if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
		{
			@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );	
		}
		CurrnecyExchangeLiquidityGmoList* liquidityList = [ CurrnecyExchangeLiquidityGmoList listWithPgresult: r ];
		PQclear( r );

		if ( [ liquidityList count ] < averageLength )
		{
			continue;
		}
		
		for ( int i = averageLength - 1, count = [ liquidityList count ]; i < count; i++ )
		{
			double liquidityValue = 0.0;
			for ( int j = i - averageLength + 1; j < i + 1; j++ )
			{
				CurrencyExchangeLiquidityGmo* gmo = [ liquidityList objectAtIndex: j ];
				liquidityValue += gmo.liquidity;
			} 
			liquidityValue /= averageLength;

			CurrencyExchangeLiquidityGmo* gmo = [ liquidityList objectAtIndex: i ];
			CurrencyExchangeLiquidityMaGmo* maGmo = [ [ CurrencyExchangeLiquidityMaGmo alloc ] init ];
			maGmo.code = gmo.code;
			maGmo.targetCurrencyCode = gmo.targetCurrencyCode;
			maGmo.exchangeDate = gmo.exchangeDate;
			maGmo.liquidity = liquidityValue;
			maGmo.averageLength = averageLength;

			s = [ maGmo queryInsert ];
//			NSLog( @"%@", s );
			if ( DbResponse_Success != [ ope executeQuery: s intoResult: & r ] )
			{
				@throw( [ NSException exceptionWithName: kText_DbOperationNotSuccess reason: [ ope errorMessage ] userInfo: nil ] );	
			}
		}
	}
}

@end
